package com.qingcheng.dao;

import com.qingcheng.pojo.report.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * 商品类目销售分析表 访问接口
 */
public interface CategoryReportMapper extends Mapper<CategoryReport> {


    @Select(" SELECT   oi.`category_id1`categoryId1,  oi.`category_id2`categoryId2,  oi.`category_id3`categoryId3,  DATE_FORMAT(o.`pay_time`, '%Y-%m-%d') countDate," + "  SUM(oi.`num`) num,  SUM(oi.`money`) money " +
            "FROM  tb_order o,  `tb_order_item` oi WHERE o.`id` = oi.`order_id`   AND o.`pay_status` = '1'   AND DATE_FORMAT(o.`pay_time`, '%Y-%m-%d') =#{date} " +
            "GROUP BY oi.`category_id1`,  oi.`category_id2`,  oi.`category_id3`,  DATE_FORMAT(o.`pay_time`, '%Y-%m-%d')")
    public List<CategoryReport> categoryReport(@Param("date") LocalDate date);

    /**
     * 按时间段统计一级目录
     * @param beginDate
     * @param endDate
     * @return
     */
    @Select("SELECT `category_id1` categoryId,c.name categoryName, SUM(num) num,SUM(money) money FROM `tb_category_report` r ,v_category c WHERE r.category_id1=c.id AND `count_date`>=#{beginDate} AND " +
            "`count_date` <= #{endDate} GROUP BY`category_id1` ,c.name")
    public List<Map> category1Count(@Param("beginDate") String beginDate, @Param("endDate") String endDate);
}
